/*****
*Created/Source by	:sfibich
*ScriptName			:metadata.view.logStatus.sql
*Version			:1.0.120123
*Purpose			:A view of the metadata.logging table that shows the
*					 current status of processes.  Certain assumptions
*					 are made to deterime process status.
*****/
SET NOCOUNT ON;

if exists (select 1 from sys.views where name='logStatus' and
schema_name(schema_id)='metadata')
BEGIN
	drop view metadata.logStatus
	print 'SUCCESS! drop view metadata.logStatus'
END
go
create view metadata.logStatus
as
select
s_info.start_logging_id as [logging_id] 
,ml1.calling_sql_name 
,cast(ml1.date_logged as smalldatetime) as [start_date]
,cast(ml2.date_logged as smalldatetime) as [end_date]
,case 
	when ml2.step<>ml1.total_steps then	DATEDIFF(ss,ml1.date_logged,GETDATE())
	when ml1.calling_sql_name <> ml2.calling_sql_name then DATEDIFF(ss,ml1.date_logged,GETDATE())	 
	else DATEDIFF(ss,ml1.date_logged,ml2.date_logged) 
end as [run_time_in_seconds]
,case
	when ml2.step=0 then 'Error' 
	when ml1.calling_sql_name = ml2.calling_sql_name
	then case when ml2.step=ml1.total_steps then 'Finished'
		else 'Running'
	end
	when ml1.calling_sql_name <> ml2.calling_sql_name then 'Running'
	else 'Error'
end as [status] 
from
(
	select
	set_id
	,MIN(logging_id) start_logging_id
	from
	metadata.logging
	group by
	set_id
)s_info
inner join
metadata.logging ml1
on
ml1.logging_id =s_info.start_logging_id 
inner join
(
	select
	set_id
	,MAX(logging_id) end_logging_id
	from
	metadata.logging
	group by
	set_id
)e_info
on
e_info.set_id =s_info.set_id 
inner join
metadata.logging ml2
on
ml2.logging_id =e_info.end_logging_id 
go

if exists (select 1 from sys.views where name='logStatus' and SCHEMA_NAME(schema_id)='metadata')
BEGIN
	merge metadata.utilityInfo as t
	using (
	select 
	'metadata.logStatus' as info_desc
	,'1.0.120123' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
	print 'SUCCESS! create view metadata.logStatus';
END
ELSE
	print 'FAILURE! drop/create view metadata.logStatus';
